﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using e3 = CodeCamp.Model;
using e3d = CodeCamp.EFDAL;
using CCMEM = CodeCamp.Models.EventMgmt;

namespace CodeCamp.Controllers
{
    //*** TODO: add IDisposable and dispose the EntityFramework context
  public class CodeCampDataRepository : IDisposable
  {
    private bool _disposed;

    public static class IdentityIssuers
    {
        /// <summary>
        /// Used when system is set to use local Membership Provider
        /// </summary>
        public const string CodeCamp = "CodeCamp";
        /// <summary>
        /// When presenters are added for prior events without adding login
        /// </summary>
        public const string none = "none";
    }

    public static class IdentityProviders
    {
        /// <summary>
        /// Used when system is set to use local Membership Provider
        /// </summary>
        public const string CodeCamp = "CodeCamp";
        /// <summary>
        /// When presenters are added for prior events without adding login
        /// </summary>
        public const string none = "none";
    }

    internal static class ClaimIssuers
    {
        internal const string LOCAL_AUTHORITY = "LOCAL AUTHORITY";
    }

    public static class ClaimTypes
    {
        public const string username = "username";
        internal const string nameidentifier_2005 = "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier";
        internal const string emailaddress_2005 = "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress";
        internal const string name_2005 = "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name";
        internal const string identityprovider_2010 = "http://schemas.microsoft.com/accesscontrolservice/2010/07/claims/identityprovider";
    }
      
    public CodeCampDataRepository()
    {
      _disposed = false;
    }


    private e3d.CodeCampDbContext _dbContext = new e3d.CodeCampDbContext();

    internal e3d.CodeCampDbContext EFContext { get { return _dbContext; } }

    //*************************
    #region IDisposable Members

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    private void Dispose(bool disposing)
    {
      if (!this._disposed)
      {
        this._disposed = true;
        if (_dbContext != null)
        {
            _dbContext.Dispose();
            _dbContext = null;
        }
      }
    }

    #endregion //IDisposable Members

    //************
    #region Events

    public IQueryable<e3.Event> GetEvents()
    { return EFContext.Events; }

    public IQueryable<e3.Event> GetEventsInDisplayOrder()
    { return EFContext.Events.OrderBy(e => e.DisplaySortOrder); }

    public e3.Event GetEvent(int eventId)
    {
        return GetEvent(eventId, true);
    }

    public e3.Event GetEvent(int eventId, bool publicOnly)
    {
        return EFContext.Events.FirstOrDefault(e => e.Id == eventId && (!publicOnly || e.EventIsPublic)); 
    }

    //public e3.Event GetEvent(string shortName)
    //{
    //    return GetEvent(shortName, true);
    //}

    public e3.Event GetEvent(string shortName, bool publicOnly)
    {
        return EFContext.Events.FirstOrDefault(e => e.ShortName == shortName && (!publicOnly || e.EventIsPublic));
    }

    public string GetEventDisplayName(string shortName)
    {
        var displayName = EFContext.Events.Where(e => e.ShortName == shortName).Select(e2 => e2.DisplayName).FirstOrDefault();
        return displayName;
    }

    #endregion

    #region Sessions

    public IQueryable<e3.Session> GetSessions(int eventId)
    { return EFContext.Sessions.Where(s => s.EventId == eventId); }

    public IQueryable<e3.Session> GetPublicSessions(int eventId)
    {
        int status_Submitted = (int)e3.Session.SessionStatus.Submitted;
        int status_Selected = (int)e3.Session.SessionStatus.Selected;
        int status_LateCancel = (int)e3.Session.SessionStatus.LateCancel;
        return EFContext.Sessions
            .Where(s => s.EventId == eventId)
            .Where(s => (s.SessionStatusId == status_Submitted) || (s.SessionStatusId == status_Selected)
                     || (s.SessionStatusId == status_LateCancel))
            ;
    }

    public e3.Session GetSession(int eventId, int sessionId)
    { return EFContext.Sessions.FirstOrDefault(s => s.EventId == eventId && s.Id == sessionId); }

    public void Add(e3.Session session)
    { EFContext.Sessions.Add(session); }

    public void Delete(e3.Session session)
    { EFContext.Sessions.Remove(session); }

    public IQueryable<e3.Session> GetUnscheduledSessionsWithTagsAndPresenters(int eventId)
    {
        var eventTimeslotIds = EFContext.Timeslots
                                .Where(ts => ts.EventId == eventId)
                                .Select(ts => ts.Id);

        var schedSessionIds = from ss in EFContext.ScheduledSessions
                              where (eventTimeslotIds).Contains(ss.TimeslotId)
                              select ss.SessionId;

        var unscheduledSessions = from s in EFContext.Sessions
                                  where s.EventId == eventId
                                  where !(schedSessionIds).Contains(s.Id)
                                  select s;
        var sessionsPlus = unscheduledSessions
                                    .Include(s => s.SessionPresenters.Select(sp => sp.Presenter))
                                    .Include(s => s.SessionTags)
                                    .Include(s => s.SessionTags.Select(st => st.Tag));
        return sessionsPlus;
    }

    public IQueryable<e3.Session> GetUnscheduledSessions(int eventId)
    {
        var eventTimeslotIds = EFContext.Timeslots
                                .Where(ts => ts.EventId == eventId)
                                .Select(ts => ts.Id);

        var schedSessionIds = from ss in EFContext.ScheduledSessions
                              where (eventTimeslotIds).Contains(ss.TimeslotId)
                              select ss.SessionId;

        var unscheduledSessions = from s in EFContext.Sessions
                                  where s.EventId == eventId
                                  where !(schedSessionIds).Contains(s.Id)
                                  select s;

        return unscheduledSessions;
    }

    #endregion

    #region ScheduledSession

    public e3.ScheduledSession GetScheduledSession( int sessionId )
    {
        var session = EFContext.Sessions.Where(s => s.Id == sessionId).FirstOrDefault();
      if ( session == null )
        return null;

      return session.ScheduledSessions.FirstOrDefault();
    }

    #endregion

    #region Timeslots

    public IQueryable<e3.Timeslot> GetTimeslots(int eventId)
    {
        return EFContext.Timeslots
          .Where(t => t.EventId == eventId)
          .OrderBy(t => t.Date)
          .ThenBy(t => t.StartTime);
    }

    public e3.Timeslot GetTimeslot(int eventId, int timeslotId)
    {
        return EFContext.Timeslots
            .Where(t => t.EventId == eventId)
            .Where(t => t.Id == timeslotId)
            .FirstOrDefault();
    }

    #endregion

    #region Buildings

    internal IQueryable<e3.Building> GetBuildings(int eventId)
    { return EFContext.Buildings.Where(b => b.EventId == eventId); }

    internal int GetBuildingId(int eventId, string name)
    {
        return EFContext.Buildings
                    .Where(b => b.EventId == eventId)
                    .Where(b => b.Name == name)
                    .Select(b => b.Id)
                    .FirstOrDefault();
    }

    #endregion

    #region Roles

    public e3.Role AddRole(string roleName)
    {
        e3.Role role = GetRole(roleName);
        if (role == null)
        {
            role = new e3.Role
            {
                RoleName = roleName
            };
            EFContext.Roles.Add(role);
            //EFContext.SaveChanges();
        }
        return role;
    }

    public e3.Role4Event AddRole4Event(string roleName)
    {
        e3.Role4Event role = GetRole4Event(roleName);
        if (role == null)
        {
            role = new e3.Role4Event
            {
                RoleName = roleName
            };
            EFContext.Roles4Events.Add(role);
            //EFContext.SaveChanges();
        }
        return role;
    }

    public void AddSiteUserToEventRole(e3.Event ccEvent, e3.SiteUser siteUser, string createdBy, string roleName, bool createRoleIfNotExists)
    {
        if (ccEvent == null)
            throw new ArgumentNullException("ccEvent");
        if (siteUser == null)
            throw new ArgumentNullException("siteUser");
        if (string.IsNullOrWhiteSpace(roleName))
            throw new ArgumentException(string.Format("roleName is null or empty.", roleName), roleName);

        e3.Role4Event role = GetRole4Event(roleName);
        if ((role == null) && (createRoleIfNotExists))
        {
            role = new e3.Role4Event
            {
                RoleName = roleName
            };
        }
        if (role != null)
        {
            //role.Id will be 0 if new role, but that's ok as no one can yet be in a new role
            e3.UserInEventRole uir = EFContext.UsersInEventRoles
                                .Where(ur => ur.EventId == ccEvent.Id && ur.SiteUserId == siteUser.Id && ur.Role4EventId == role.Id)
                                .FirstOrDefault();

            if (uir == null)
            {
                uir = new e3.UserInEventRole
                {
                    CreatedBy = createdBy,
                    Event = ccEvent,
                    EventId = ccEvent.Id,
                    Role4Event = role,
                    Role4EventId = role.Id,
                    SiteUser = siteUser,
                    SiteUserId = siteUser.Id
                };
                EFContext.UsersInEventRoles.Add(uir);
            }
        }
    }

    public void AddSiteUserToRole(e3.SiteUser siteUser, string createdBy, string roleName, bool createRoleIfNotExists)
    {
        if (siteUser == null)
            throw new ArgumentNullException("siteUser");
        if (string.IsNullOrWhiteSpace(roleName))
            throw new ArgumentException(string.Format("roleName is null or empty.", roleName), roleName);

        e3.Role role = GetRole(roleName);
        if ((role == null) && createRoleIfNotExists)
        {
            role = new e3.Role
            {
                RoleName = roleName
            };
        }
        if (role != null)
        {
            //role.Id will be 0 if new role, but that's ok as no one can yet be in a new role
            e3.UserInRole uir = EFContext.UsersInRoles
                                .Where(ur => ur.SiteUserId == siteUser.Id && ur.RoleId == role.Id)
                                .FirstOrDefault();

            if (uir == null)
            {
                uir = new e3.UserInRole
                {
                    CreatedBy = createdBy,
                    Role = role,
                    RoleId = role.Id,
                    SiteUser = siteUser,
                    SiteUserId = siteUser.Id
                };
                EFContext.UsersInRoles.Add(uir);
            }
        }
    }

    public e3.Role GetRole(string roleName)
    {
        return EFContext.Roles
                .Where(r => r.RoleName == roleName)
                .FirstOrDefault();
    }

    public int GetRoleId(string roleName)
    {
        return EFContext.Roles
                .Where(r => r.RoleName == roleName)
                .Select(r => r.Id)
                .FirstOrDefault();
    }

    public e3.Role4Event GetRole4Event(string roleName)
    {
        return EFContext.Roles4Events
                .Where(r => r.RoleName == roleName)
                .FirstOrDefault();
    }

    public int GetRole4EventId(string roleName)
    {
        return EFContext.Roles4Events
                .Where(r => r.RoleName == roleName)
                .Select(r => r.Id)
                .FirstOrDefault();
    }

    public List<string> GetSiteUserEventRoles(int siteUserID, int eventID)
    {
        var roles =
            (from ur in EFContext.UsersInEventRoles
                join r in EFContext.Roles4Events
                on ur.Role4EventId equals r.Id
                where ur.EventId == eventID
                where ur.SiteUserId == siteUserID
             orderby r.RoleName
             select new
             {
                 r.RoleName
             })
             .Select(r => r.RoleName).ToList();

        return roles;
    }

    public List<string> GetSiteUserSiteRoles(int siteUserID)
    {
        var roles =
            (from ur in EFContext.UsersInRoles
             join r in EFContext.Roles
             on ur.RoleId equals r.Id
             where ur.SiteUserId == siteUserID
             orderby r.RoleName
             select new
             {
                 r.RoleName
             })
             .Select(r => r.RoleName).ToList();

        return roles;
    }

    public List<string> GetSiteUserRoles(int siteUserID, int eventID)
    {
        List<string> roles = GetSiteUserEventRoles(siteUserID, eventID);
        List<string> siteRoles = GetSiteUserSiteRoles(siteUserID);
        foreach (string role in siteRoles)
        {
            if (!roles.Contains(role))
                roles.Add(role);
        }
        return roles;
    }

    public e3.UserInEventRole GetUserInEventRole(int siteUserID, int eventID, string roleName)
    {
        int roleId = GetRole4EventId(roleName);
        if (roleId == 0)
            return null;

        return EFContext.UsersInEventRoles
                                .Where(ur => ur.SiteUserId == siteUserID)
                                .Where(ur => ur.EventId == eventID)
                                .Where(ur => ur.Role4EventId == roleId)
                                .FirstOrDefault();
    }

    public e3.UserInRole GetUserInRole(int siteUserID, string roleName)
    {
        int roleId = GetRoleId(roleName);
        if (roleId == 0)
            return null;

        return EFContext.UsersInRoles
                                .Where(ur => ur.SiteUserId == siteUserID)
                                .Where(ur => ur.RoleId == roleId)
                                .FirstOrDefault();
    }

    public bool SiteUserIsInEventRole(int siteUserID, int eventID, string roleName)
    {
        int roleId = GetRole4EventId(roleName);
        if (roleId == 0)
            return false;

        int uierSiteUserID = EFContext.UsersInEventRoles
                                .Where(ur => ur.SiteUserId == siteUserID)
                                .Where(ur => ur.EventId == eventID)
                                .Where(ur => ur.Role4EventId == roleId)
                                .Select(ur => ur.SiteUserId)
                                .FirstOrDefault();

        return (siteUserID == uierSiteUserID);
    }

    public bool SiteUserIsInRole(int siteUserID, string roleName)
    {
        int roleId = GetRoleId(roleName);
        if (roleId == 0)
            return false;

        int uirSiteUserID = EFContext.UsersInRoles
                                .Where(ur => ur.SiteUserId == siteUserID)
                                .Where(ur => ur.RoleId == roleId)
                                .Select(ur => ur.SiteUserId)
                                .FirstOrDefault();

        return (siteUserID == uirSiteUserID);
    }

    #endregion //Roles

    #region Rooms

    internal e3.Room GetRoom(int roomId)
    { return EFContext.Rooms.Where(room => room.Id == roomId).FirstOrDefault(); }

    internal e3.Room GetRoomWithBuilding(int roomId)
    {
        return EFContext.Rooms
                    .Include("Building")
                    .Where(room => room.Id == roomId)
                    .FirstOrDefault();
    }

    internal e3.Room GetRoomWithScheduledSessions(int roomId)
    {
        return EFContext.Rooms
            .Include("ScheduledSessions")
            .Where(room => room.Id == roomId)
            .FirstOrDefault();
    }

    internal int GetRoomId(int buildingId, string name)
    {
        return EFContext.Rooms
                    .Where(r => r.BuildingId == buildingId)
                    .Where(b => b.Name == name)
                    .Select(b => b.Id)
                    .FirstOrDefault();
    }

    internal IQueryable<e3.Room> GetRooms(int buildingId)
    {
        return EFContext.Rooms
                    .Where(room => room.BuildingId == buildingId);
    } 

    #endregion

    #region Presenters

    public IQueryable<e3.Presenter> GetPresenters(int eventId)
    { return EFContext.Presenters.Where(p => p.EventId == eventId); }

    public IQueryable<CCMEM.EventMgmtUserViewModel> GetQueryablePresentersListForMgmt(int eventId)
    {
        var presenterList = from pr in EFContext.Presenters
                      join su in EFContext.SiteUsers on pr.SiteUserId equals su.Id
                      where pr.EventId == eventId
                      select new CCMEM.EventMgmtUserViewModel
                      {
                          IsApproved = su.IsApproved,
                          IsLockedOut = su.IsLockedOut,
                          LastActivityDate = su.LastOnlineActivityUTC,
                          LastLoginDate = su.LastLoginUTC,
                          PresenterCreationDate = pr.CreatedUTC,
                          PresenterEmailAddress = pr.EmailAddress,
                          PresenterEmailCount = pr.EmailItems.Count(),
                          PresenterFirstName = pr.FirstName,
                          PresenterLastName = pr.LastName,
                          PresenterId = pr.Id,
                          PresenterName = String.Concat(pr.FirstName, " ", pr.LastName).Trim(),
                          SessionCount = pr.SessionPresenters.Count(),
                          SiteUserEmailAddress = su.EmailAddress,
                          SiteUserEmailCount = (from ei in su.EmailItems where ei.EventId == eventId && !ei.PresenterId.HasValue select 1).Count(),
                          SiteUserId = su.Id,
                          UserCreationDate = su.CreatedUTC
                      };
        return presenterList;
    }

    //public e3.Presenter GetPresenter(int presenterId)
    //{ return EFContext.Presenters.FirstOrDefault(p => p.Id == presenterId); }

    public e3.Presenter GetPresenter(int eventId, int presenterId)
    { return EFContext.Presenters.FirstOrDefault(p => (p.EventId == eventId) && (p.Id == presenterId)); }

      //TODO: revisit this when adding claims-based auth
    //public e3.Presenter GetPresenter(int eventId, string userIdentityName)
    //{
    //    return EFContext.Presenters.FirstOrDefault(p => (p.EventId == eventId) && (p.UserIdentityProvider != IdentityProviders.none) && (p.UserIdentityClaim == userIdentityName));
    //}

    //public int GetPresenterId(int eventId, string userIdentityName)
    //{
    //    var presenterId = EFContext.Presenters.Where(p => p.EventId == eventId && p.UserIdentityClaim == userIdentityName).Select(p2 => p2.Id).FirstOrDefault();
    //    return presenterId;
    //}

    public e3.Presenter GetDisconnectedPresenter(int eventId, string emailAddress)
    {
        return EFContext.Presenters.FirstOrDefault(p =>
            (p.EventId == eventId)
            && !p.SiteUserId.HasValue
            && (p.EmailAddress == emailAddress));
    }

    public int GetDisconnectedPresenterId(int eventId, string emailAddress)
    {
        return EFContext.Presenters.Where(p =>
            (p.EventId == eventId)
            && !p.SiteUserId.HasValue
            && (p.EmailAddress == emailAddress)).Select(p2 => p2.Id).FirstOrDefault();
    }

    public e3.Presenter GetPriorPresenterProfile(int eventId, string emailAddress)
    {
        var eventStartDate = EFContext.Events.Where(e => e.Id == eventId).Select(e2 => e2.EventStartDate).FirstOrDefault();
        if (eventStartDate > new DateTime(2000, 1, 1))
        {
            var prVal =
                (from ev in EFContext.Events
                 join pr in EFContext.Presenters
                 on ev.Id equals pr.EventId
                 where ev.EventIsPublic
                 where pr.EmailAddress == emailAddress
                 orderby ev.EventStartDate descending
                 select new
                 {
                     pr.Id
                 }).FirstOrDefault();

            if (prVal != null)
            {
                return EFContext.Presenters.Where(p => p.Id == prVal.Id).FirstOrDefault();
            }
        }
        return null;
    }

    public void Add(e3.Presenter presenter)
    { EFContext.Presenters.Add(presenter); }

    public void Delete(e3.Presenter presenter)
    { EFContext.Presenters.Remove(presenter); }

    #endregion

    #region SessionPresenters

    public void Add(e3.SessionPresenter sessionPresenter)
    { EFContext.SessionPresenters.Add(sessionPresenter); }

    #endregion

    #region SiteUsers

    public e3.SiteUser GetSiteUser(int siteUserId)
    { return EFContext.SiteUsers.FirstOrDefault(su => (su.Id == siteUserId)); }

    internal e3.SiteUser GetSiteUser(string identityProvider, string claimType, string claim)
    {
        var siteUser = EFContext.SiteUsers.Where(u =>
            u.UserIdentityProvider == identityProvider
            && u.UserIdentityClaimType == claimType
            && u.UserIdentityClaim == claim).FirstOrDefault();

        return siteUser;
    }

    internal e3.SiteUser GetSiteUser(string identityProvider, string claimType, string claim, bool userIsOnline)
    {
        var siteUser = GetSiteUser(identityProvider, claimType, claim);
        if ((siteUser != null) && userIsOnline)
        {
            try
            {
                siteUser.LastOnlineActivityUTC = DateTime.UtcNow;
                Save();
            }
            catch (OptimisticConcurrencyException ex)
            {
                int foo = 1;
            }
        }
        return siteUser;
    }

    internal e3.SiteUser GetSiteUser(string identityProvider, string claimType, string claim, bool userIsOnline, bool atLogin)
    {
        var siteUser = GetSiteUser(identityProvider, claimType, claim);
        if ((siteUser != null) && userIsOnline)
        {
            try
            {
                siteUser.LastOnlineActivityUTC = DateTime.UtcNow;
                if (atLogin)
                    siteUser.LastLoginUTC = DateTime.UtcNow;
                Save();
            }
            catch (OptimisticConcurrencyException ex)
            {
                int foo = 1;
            }
        }
        return siteUser;
    }

    internal e3.SiteUser GetSiteUserAtLogon(string identityProvider, string claimType, string claim)
    {
        var siteUser = GetSiteUser(identityProvider, claimType, claim);
        if (siteUser != null)
        {
            try
            {
                siteUser.LastLoginUTC = DateTime.UtcNow;
                siteUser.LastOnlineActivityUTC = DateTime.UtcNow;
                Save();
            }
            catch (OptimisticConcurrencyException ex)
            {
                int foo = 1;
            }
        }
        return siteUser;
    }

    #endregion //SiteUsers

    #region Tags

    public IQueryable<e3.BannedTag> GetBannedTags()
    { return EFContext.BannedTags; }

    public IQueryable<e3.Tag> GetTags(int eventId)
    { return EFContext.Tags.Where(t => t.EventId == eventId).OrderBy(t => t.Name); }

    public e3.Tag GetTag(int tagId)
    { return EFContext.Tags.Where(t => t.Id == tagId).FirstOrDefault(); }

    public e3.Tag GetTag(int eventId, string tagName)
    { return EFContext.Tags.Where(t => (t.EventId == eventId) && (t.Name == tagName)).FirstOrDefault(); }

    public void Add(e3.Tag tag)
    { EFContext.Tags.Add(tag); }

    #endregion

    #region SessionTags

    public IQueryable<e3.SessionTag> GetSessionTags(int sessionId)
    { return EFContext.SessionTags.Where(st => st.SessionId == sessionId); }

    public void Add(e3.SessionTag sessionTag)
    { EFContext.SessionTags.Add(sessionTag); }

    public void Delete(e3.SessionTag sessionTag)
    { EFContext.SessionTags.Remove(sessionTag); }

    public IEnumerable<e3.Session> GetTaggedSessions(int eventId, int tagId, out string tagName)
    {
        tagName = null;
        var tag = EFContext.Tags
                    .Where(t => t.EventId == eventId && t.Id == tagId)
                    .FirstOrDefault();
        if (tag != null)
        {
            tagName = tag.Name;
            int status_Submitted = (int)e3.Session.SessionStatus.Submitted;
            int status_Selected = (int)e3.Session.SessionStatus.Selected;

            var taggedSessions = tag.SessionTags
                .Where(st => (st.Session.SessionStatusId == status_Submitted) || (st.Session.SessionStatusId == status_Selected))
                .Select(st => st.Session);
            return taggedSessions;
        }
        return null;
    }

    #endregion

    //#region Attendees

    //public Attendee GetAttendee( int eventId, string name )
    //{ return context02.Attendees.FirstOrDefault( a => (a.EventId == eventId) && (a.Name == name) ); }

    //public Attendee GetAttendee( int id )
    //{ return context02.Attendees.FirstOrDefault( a => a.Id == id ); }

    //private Attendee CreateAttendee( int eventId, string name, bool isAnonymous )
    //{
    //  var attendee = new Attendee { EventId = eventId, Name = name, IsAnonymous = isAnonymous };
    //  context02.Attendees.InsertOnSubmit( attendee );
    //  context02.SubmitChanges();

    //  return attendee;
    //}

    //public Attendee CreateAttendee( int eventId, string name )
    //{ return CreateAttendee( eventId, name, false ); } // create non-anonymous attendee

    //public Attendee CreateAnonymousAttendee( int eventId )
    //{
    //  var attendee = CreateAttendee( eventId, string.Empty, true ); // create anonymous attendee

    //  // rename attendee as AnonymousN where N = Id
    //  attendee.Name = string.Format( "Anonymous{0}", attendee.Id );
    //  context02.SubmitChanges();

    //  return attendee;
    //}

    //public void Add( Attendee attendee )
    //{ context02.Attendees.InsertOnSubmit( attendee ); }

    //#endregion

    //#region AttendeeSessionPicks

    //public void Add( AttendeeSessionPick attendeeSessionPick )
    //{ context02.AttendeeSessionPicks.InsertOnSubmit( attendeeSessionPick ); }

    //public void Delete( AttendeeSessionPick attendeeSessionPick )
    //{ context02.AttendeeSessionPicks.DeleteOnSubmit( attendeeSessionPick ); }

    //#endregion

    #region SpeakerRate

    public e3.SpeakerRateSpeaker GetSpeakerRateSpeaker(int presenterId)
    { return EFContext.SpeakerRateSpeakers.FirstOrDefault(s => s.PresenterId == presenterId); }

    public void Add(e3.SpeakerRateSpeaker speakerRateSpeaker)
    { EFContext.SpeakerRateSpeakers.Add(speakerRateSpeaker); }

    public void Delete(e3.SpeakerRateSpeaker speakerRateSpeaker)
    { EFContext.SpeakerRateSpeakers.Remove(speakerRateSpeaker); }

    #endregion

    #region Sponsors

    public IQueryable<e3.Sponsor> GetSponsors(int eventId)
    { return EFContext.Sponsors.Where(s => s.EventId == eventId); }

    public e3.Sponsor GetSponsor(int eventId, int sponsorId)
    { return EFContext.Sponsors.FirstOrDefault(s => s.EventId == eventId && s.Id == sponsorId); }

    public void Add(e3.Sponsor sponsor)
    { EFContext.Sponsors.Add(sponsor); }

    #endregion

    #region CommunitySponsor

    public IQueryable<e3.CommunitySponsor> GetCommunitySponsors(int eventId)
    {
        return
          from s in EFContext.CommunitySponsors
          where s.EventId == eventId
          orderby s.Name
          select s;
    }

    #endregion

    #region IndividualContributor

    public IQueryable<e3.IndividualContributor> GetIndividualContributors(int eventId)
    {
        return
          from s in EFContext.IndividualContributors
          where s.EventId == eventId
          orderby s.LastName
          select s;
    }

    #endregion

    #region Volunteers

    public IQueryable<e3.Task> GetTasks(int eventId)
    {
        return
          from t in EFContext.Tasks
          where t.EventId == eventId
          orderby t.Tasks_Timeslots.First().TaskTimeslot.Date, t.Tasks_Timeslots.First().TaskTimeslot.StartTime
          select t;
    }

    #endregion

    public void Save()
    {
        EFContext.SaveChanges();
    }

  }
}